﻿package com.h819.core.file.excel.jxl;

import jxl.Cell;
import jxl.HeaderFooter;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.*;
import jxl.write.biff.RowsExceededException;
import org.apache.log4j.Logger;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

/**
 * @author jianghui 2008.01.25
 * 
 * \n 换行; \r 光标移至行首; \t 跳格（一个 Tab 键）
 * 
 * excel 2003 单个 sheet 最大的行数是 65000行; sheet 名称不能用中文，也不能有空格; 当一次读写行数较多的情况下，如超过 *
 * 10000 行，需要进行优化，不要一次性读写，否则会占用很大内存，可以仿照 jdbc 方式逐行处理 把记录放入多个文件.
 */

// jxl 2.6.6
// http://jexcelapi.sourceforge.net/
// Reads data from Excel 95, 97, 2000, XP, and 2003 workbooks
// Reads and writes formulas (Excel 97 and later only)
// Generates spreadsheets in Excel 2000 format
// Supports font, number and date formatting
// Supports shading, bordering, and coloring of cells
// Modifies existing worksheets
// Is internationalized, enabling processing in almost any locale, country,
// language, or character encoding (formulas are currently only supported in
// English, French, Spanish, and German, but more can be added if translated)
// Supports copying of charts
// Supports insertion and copying of images into spreadsheets
// Supports logging with Jakarta Commons Logging, log4j, JDK 1.4 Logger, etc
// ...and much more
public class ExcelUtils {

	private Logger log = Logger.getLogger(ExcelUtils.class);

	/**
	 * 生成一个Excel文件
	 * 生成的文件可以：添加带有字型Formatting的对象、//添加带有字体颜色Formatting的对象、添加Number对象等
	 * 
	 * 把指定字符串内容写入到指定单元格
	 * 
	 * @param file
	 *            要生成的Excel文件名
	 * @param lineNumber
	 *            指定行数
	 * @param columNumber
	 *            指定列数数
	 * @param value
	 *            单元格内容
	 * 
	 */
	public void writeStringToExcel(File file, int lineNumber, int columNumber,
			String value) {

		WritableWorkbook wwb = null;

		// 建立文件
		try {
			// 首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象
			wwb = Workbook.createWorkbook(file);
		} catch (IOException e) {
			System.out.println("exception 0.");
			closeWritableWorkbook(wwb);
			e.printStackTrace();

		}

		if (wwb == null)
			return;

		// 开始操作excel表
		// 创建一个可写入的工作表
		// Workbook的createSheet方法有两个参数，第一个是工作表的名称，第二个是工作表在工作薄中的位置
		WritableSheet ws = wwb.createSheet("sheet1", 0);

		// 开始写入
		// 这里需要注意的是，在Excel中，第一个参数表示列，第二个参数表示行，第三个参数表示内容
		// 序号

		Label label0 = new Label(columNumber, lineNumber, value);

		try {
			// 将生成的单元格添加到工作表中
			ws.addCell(label0);

		} catch (RowsExceededException e) {
			System.out.println("exception 2.");
			e.printStackTrace();
			// 发生异常关闭
			closeWritableWorkbook(wwb);
		} catch (WriteException e) {
			System.out.println("exception 3.");
			e.printStackTrace();
			// 发生异常关闭
			closeWritableWorkbook(wwb);
		}

		// 从内存中写入文件中
		try {
			wwb.write();
			wwb.close();
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			System.out.println("exception 4.");
			e.printStackTrace();
		} catch (IOException e) {
			System.out.println("exception 5.");
			e.printStackTrace();
		}
	}

	/**
	 * 生成一个Excel文件
	 * 生成的文件可以：添加带有字型Formatting的对象、//添加带有字体颜色Formatting的对象、添加Number对象等
	 * 
	 * 
	 * @param file
	 *            要生成的Excel文件名
	 * @param stringValue
	 *            写入 excel 的内容，ArrayList 元素为 String
	 *            类型.生成的excle文件第一列为序号,第二列为ArrayList的元素.
	 * 
	 */

	// ArrayList 可以重复
	// set 不可以重复
	// map ???
	// 根据这两项,完善此函数
	public void writeArrayListStringValueToExcel(File file,
			ArrayList<String> stringValue) {

		WritableWorkbook wwb = null;

		// 建立文件
		try {
			// 首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象
			wwb = Workbook.createWorkbook(file);
		} catch (IOException e) {
			System.out.println("exception 0.");
			closeWritableWorkbook(wwb);
			e.printStackTrace();

		}

		if (wwb == null)
			return;

		// 开始操作excel表
		// 创建一个可写入的工作表
		// Workbook的createSheet方法有两个参数，第一个是工作表的名称，第二个是工作表在工作薄中的位置
		WritableSheet ws = wwb.createSheet("sheet1", 0);

		// 开始写入
		// 这里需要注意的是，在Excel中，第一个参数表示列，第二个参数表示行，第三个参数表示内容
		// 序号
		Iterator stringValue_it = stringValue.iterator();

		System.out.println(stringValue.size());

		int line_number = 0;

		Integer.toString(line_number);

		while (stringValue_it.hasNext()) {
			// 元素为 String
			String cell_String = (String) stringValue_it.next();
			// log.info("cell_String:" + cell_String);
			Label label0 = new Label(0, line_number, Integer
					.toString(line_number + 1));
			Label label1 = new Label(1, line_number, cell_String);

			// log.info("number :"+(line_number + 1));
			// log.info("cell_String :"+cell_String);

			try {
				// 将生成的单元格添加到工作表中
				ws.addCell(label0);
				ws.addCell(label1);

			} catch (RowsExceededException e) {
				System.out.println("exception 2.");
				e.printStackTrace();
				// 发生异常关闭
				closeWritableWorkbook(wwb);
			} catch (WriteException e) {
				System.out.println("exception 3.");
				e.printStackTrace();
				// 发生异常关闭
				closeWritableWorkbook(wwb);
			}
			line_number++;
		}

		// 从内存中写入文件中
		try {
			wwb.write();
			wwb.close();
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			System.out.println("exception 4.");
			e.printStackTrace();
		} catch (IOException e) {
			System.out.println("exception 5.");
			e.printStackTrace();
		}
	}

	/**
	 * 生成一个Excel文件
	 * 生成的文件可以：添加带有字型Formatting的对象、//添加带有字体颜色Formatting的对象、添加Number对象等
	 * 
	 * 一般实际操作的时候,List 多采用 ArrayList
	 * 
	 * @param file
	 *            要生成的Excel文件名
	 * @param stringValue
	 *            写入 excel 的内容，List 元素为 List 类型(子元素为String),用于一行多列类型的写入.
	 * @param ShowNO
	 *            是否显示序号
	 * 
	 */
	public void writeArrayListArrayListValueToExcel(File file,
			ArrayList<ArrayList<String>> ListValue, boolean ShowNO) {

		WritableWorkbook wwb = null;

		// 建立文件
		try {
			// 首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象
			wwb = Workbook.createWorkbook(file);
		} catch (IOException e) {
			System.out.println("exception 0.");
			closeWritableWorkbook(wwb);
			e.printStackTrace();

		}

		if (wwb == null)
			return;

		// 开始操作excel表
		// 创建一个可写入的工作表
		// Workbook的createSheet方法有两个参数，第一个是工作表的名称，第二个是工作表在工作薄中的位置
		WritableSheet ws = wwb.createSheet("sheet1", 0);

		// 开始写入
		// 这里需要注意的是，在Excel Label中，第一个参数表示列，第二个参数表示行，第三个参数表示内容
		// 序号

		int line_number = 0;// 行号

		for (List<String> li : ListValue) {

			// 顺序读取

			int colum_number = 0;// 列序号

			if (ShowNO == true) {

				// 在第一列设定行数
				Label label0 = new Label(colum_number, line_number, Integer
						.toString(line_number + 1));

				try {
					// 将生成的行数单元格添加到工作表中
					ws.addCell(label0);

				} catch (RowsExceededException e) {
					System.out.println("exception 2.");
					e.printStackTrace();
					// 发生异常关闭
					closeWritableWorkbook(wwb);
				} catch (WriteException e) {
					System.out.println("exception 3.");
					e.printStackTrace();
					// 发生异常关闭
					closeWritableWorkbook(wwb);
				}
			}

			// 开始逐列添加
			if (ShowNO == false)
				colum_number = -1;

			for (String st : li) {

				Label label1 = new Label(colum_number + 1, line_number, st);

				try {
					// 将生成的单元格添加到工作表中
					ws.addCell(label1);
				} catch (RowsExceededException e) {
					System.out.println("exception 2.");
					e.printStackTrace();
					// 发生异常关闭
					closeWritableWorkbook(wwb);
				} catch (WriteException e) {
					System.out.println("exception 3.");
					e.printStackTrace();
					// 发生异常关闭
					closeWritableWorkbook(wwb);
				}
				colum_number++;
			} // 单个元素 ArrayList 循环完毕,即一行填写完成

			line_number++;

		}

		// 从内存中写入文件中
		try {
			wwb.write();
			wwb.close();
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			System.out.println("exception 4.");
			e.printStackTrace();
		} catch (IOException e) {
			System.out.println("exception 5.");
			e.printStackTrace();
		}
	}

	/**
	 * 搜索某一个文件中是否包含某个关键字
	 * 
	 * @param file
	 *            待搜索的文件
	 * @param keyWord
	 *            要搜索的关键字
	 * @return
	 */
	public static boolean searchKeyWord(File file, String keyWord) {
		boolean res = false;
		Workbook wb = null;
		try {
			// 构造Workbook（工作薄）对象
			wb = Workbook.getWorkbook(file);
			if (wb == null)
				return res;

			// 获得了Workbook对象之后,就可以通过它得到Sheet（工作表）对象了
			Sheet[] sheet = wb.getSheets();

			if (sheet != null && sheet.length > 0) {
				// 对每个工作表进行循环
				boolean breakSheet = false;
				for (int sheetNum = 0; sheetNum < sheet.length; sheetNum++) {
					if (breakSheet)
						break;
					// 得到当前工作表的行数
					int rowNum = sheet[sheetNum].getRows();
					boolean breakRow = false;
					// 逐行循环
					for (int i = 0; i < rowNum; i++) {
						if (breakRow)
							break;
						// 得到当前工作表的当前行的所有单元格
						Cell[] cells = sheet[sheetNum].getRow(i);
						if (cells != null && cells.length > 0) {
							boolean breakCell = false;
							// 每个单元格进行循环
							for (int k = 0; k < cells.length; k++) {
								if (breakCell)
									break;
								// 读取当前单元格的值
								String cellValue = cells[k].getContents();
								if (cellValue == null)
									continue;
								if (cellValue.contains(keyWord)) {
									res = true;
									breakCell = true;
									breakRow = true;
									breakSheet = true;
								}
							}
						}
					}
				}
			}
		} catch (BiffException e) {
			e.printStackTrace();
			return res;
		} catch (IOException e) {
			e.printStackTrace();
			return res;
		} finally {// 最后关闭资源,释放内存
			wb.close();
		}
		return res;
	}

	/**
	 * 往Excel中插入图片,目前只支持png
	 * 
	 * @param dataSheet
	 *            待插入的工作表
	 * @param col
	 *            图片从该列开始
	 * @param row
	 *            图片从该行开始
	 * @param width
	 *            图片所占的列数
	 * @param height
	 *            图片所占的行数
	 * @param imgFile
	 *            要插入的图片文件
	 */
	public static void insertImg(WritableSheet dataSheet, int col, int row,
			int width, int height, File imgFile) {
		WritableImage img = new WritableImage(col, row, width, height, imgFile);
		dataSheet.addImage(img);
	}

	/**
	 * 向Excel中加入页眉页脚
	 * 
	 * @param dataSheet
	 *            待加入页眉的工作表
	 * @param left
	 * @param center
	 * @param right
	 */
	public static void setHeader(WritableSheet dataSheet, String left,
			String center, String right) {
		HeaderFooter hf = new HeaderFooter();
		hf.getLeft().append(left);
		hf.getCentre().append(center);
		hf.getRight().append(right);
		// 加入页眉
		dataSheet.getSettings().setHeader(hf);
		// 加入页脚
		// dataSheet.getSettings().setFooter(hf);
	}

	//
	// wwb.close() 函数要用 try catch ，在这里包装起来，仅仅用作简化代码
	// 当 wwb 被关闭之后，其数值也不是 null，同时也不能再次被调用，jxl 没有提供判断是否关闭的方法，不同于数据的连接
	// 所以该关闭函数只能调用一次
	public static void closeWritableWorkbook(WritableWorkbook wwb) {

		try {

			if (wwb != null) {

				wwb.close();
				wwb = null;
			}

		} catch (WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			wwb = null;
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			wwb = null;
		}
	}

	public static void main(String[] args) {

		ExcelUtils ex = new ExcelUtils();
		WritableWorkbook imgBook = null;
		WritableWorkbook hfBook = null;
		File imgFile = null;

		// ........... 测试写入图片
		// try {
		// // 创建一个工作薄
		// imgBook = Workbook.createWorkbook(new File(
		// "D:/test1.xls"));
		// // 待插入的工作表
		// WritableSheet imgSheet = imgBook.createSheet("Images", 0);
		// // 要插入的图片文件
		// imgFile = new File("D:/1.png");
		// // 图片插入到第二行第一个单元格,长宽各占六个单元格
		// insertImg(imgSheet, 0, 1, 6, 6, imgFile);
		// imgBook.write();
		//					
		// } catch (IOException e) {
		// e.printStackTrace();
		// }finally{// 关闭资源,释放内存
		// try {
		// if(imgBook!=null){
		// imgBook.close();}
		// } catch (IOException e) {
		// e.printStackTrace();
		// } catch (WriteException e) {
		// e.printStackTrace();
		// }
		// }

		// ........... 测试写入页眉页脚
		// try {
		// // 创建一个工作薄
		// hfBook = Workbook.createWorkbook(new File(
		// "D:/test2.xls"));
		// // 待插入的工作表
		// WritableSheet hfSheet = hfBook.createSheet("hf", 0);
		// setHeader(hfSheet, "页眉左","页眉中","页眉右");
		// hfBook.write();
		//							
		// } catch (IOException e) {
		// e.printStackTrace();
		// }finally{// 关闭资源,释放内存
		// try {
		// if(hfBook!=null){
		// hfBook.close();}
		// } catch (IOException e) {
		// e.printStackTrace();
		// } catch (WriteException e) {
		// e.printStackTrace();
		// }
		// }

		// // 写
		// ex.writeExcel(new File("D:/gm.xls"),"2005");

		// //查找关键字
		// System.out.print(searchKeyWord(new File("D:/write.xls"),"中心"));

		// 读测试
		// Map<Integer, Map<Integer, String>> result = ex.readExcel(new File(
		// "D:/1.xls"));
		// Set<Integer> s = result.keySet();
		// Iterator<Integer> it_s = s.iterator();
		//
		// while (it_s.hasNext()) {// 循环行
		// // 元素为 TreeMap
		// Integer lineNumber = it_s.next();
		// Map line = (TreeMap) result.get(lineNumber);
		//
		// Set s_sub = line.keySet();
		// Iterator it_s_sub = s_sub.iterator();
		//
		// // 顺序读取
		// while (it_s_sub.hasNext()) {// 循环指定行的列
		//
		// Integer columnNumber = (Integer) it_s_sub.next();
		// String cellContent = (String) line.get(columnNumber);
		// System.out.print(lineNumber + "," + columnNumber + ","
		// + cellContent + ";");
		// }
		//
		// System.out.print("\n");
		// }

		// 写测试
		// ArrayList writeTest = new ArrayList();
		// writeTest.add("数据1");
		// writeTest.add("数据2");
		// writeTest.add("数据3");
		// writeTest.add("数据4");
		// writeTest.add(25);

		// ex.writeArrayListToExcel(new File("D:/write.xls"), writeTest);
		// ex.writeArrayListToExcel(new File("D:/write.xls"), ex.readExcel(new
		// File("D:/1.xls")));

		
	}
}